package com.TinyBBS.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class DBCRUD {
	protected ConnectDB cdb;
	protected Connection conn;
	protected Statement stmt;
	private String query;

	/**
	 * List all record in this table
	 * 
	 * @param table
	 *            Table Name
	 * @return ResultSet
	 */
	public ResultSet fetchAll(String table) {
		ResultSet rs = null;
		try {
			cdb = ConnectDB.getInstance();
			conn = cdb.connect();
			stmt = conn.createStatement();
			query = "SELECT * FROM " + table;
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * Insert all fields of record in Map<String, Object>
	 * 
	 * @param table
	 *            Table Name
	 * @param maplist
	 *            Reference of Map<String, Object>
	 * @return Return true if insert successful else return false
	 */
	public boolean insert(String table, Map<String, Object> maplist) {
		try {
			cdb = ConnectDB.getInstance();
			conn = cdb.connect();
			List<String> key = new ArrayList<String>(maplist.keySet());
			List<Object> value = new ArrayList<Object>(maplist.values());

			query = "INSERT INTO " + table + " (";
			String marks = "";
			for (int i = 0; i < maplist.size(); i++) {
				// step 1: add field name to query
				if (i != maplist.size() - 1) {
					query += key.get(i) + ",";
					marks += "?,";
					// step 2: build number of question marks for query
				} else {
					query += key.get(i);
					marks += "?";
				}

			}
			query = query + ") VALUES (" + marks + ")";
			System.out.println(query);
			stmt = (PreparedStatement) conn.prepareStatement(query);
			for (int i = 1; i <= maplist.size(); i++) {
				// step 3: assign value
				if (value.get(i - 1) instanceof String) {
					((PreparedStatement) stmt).setString(i,
							(String) value.get(i - 1));
				} else {
					((PreparedStatement) stmt).setObject(i,
							(Object) value.get(i - 1));
				}
			}
			((PreparedStatement) stmt).execute();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		}
		return true;

	}

	/**
	 * Fetch paticular record by using 'where'
	 * 
	 * @param table
	 *            Table name
	 * @param paramList
	 *            selected field
	 * @param where
	 *            String of where
	 * @return ResultSet
	 */
	public ResultSet fetch(String table, List paramList, String where) {
		ResultSet rs = null;
		try {
			cdb = ConnectDB.getInstance();
			conn = cdb.connect();
			stmt = conn.createStatement();
			query = "SELECT ";
			for (int i = 0; i < paramList.size(); i++) {
				if (i != paramList.size() - 1) {
					query += paramList.get(i) + ",";
				} else {
					query += paramList.get(i);
				}

			}
			query += " FROM " + table + " WHERE " + where;
			// System.out.println(query);
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * fetch all record using where
	 * 
	 * @param table
	 *            Table Name
	 * @param where
	 *            retrieve records which satisfy where condition
	 * @return ResultSet
	 */
	public ResultSet fetchAll(String table, String where) {
		ResultSet rs = null;
		try {
			cdb = ConnectDB.getInstance();
			conn = cdb.connect();
			stmt = conn.createStatement();
			query = "SELECT * FROM " + table + " WHERE " + where;
			// System.out.println(query);
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * Update certain by using where
	 * 
	 * @param table
	 *            Table name
	 * @param maplist
	 *            Map<String, Object>
	 * @param where
	 *            WHERE condition
	 * @return Return true if update successful else return false
	 */
	public boolean update(String table, Map<String, Object> maplist,
			String where) {
		try {
			cdb = ConnectDB.getInstance();
			conn = cdb.connect();
			List<String> key = new ArrayList<String>(maplist.keySet());
			List<Object> value = new ArrayList<Object>(maplist.values());

			query = "UPDATE " + table + " SET ";
			for (int i = 0; i < maplist.size(); i++) {
				// step 1: add field name to query
				if (i != maplist.size() - 1) {
					query += key.get(i) + " = ?,";
					// step 2: build number of question marks for query
				} else {
					query += key.get(i) + " = ?";
				}

			}
			query = query + " WHERE " + where;
			System.out.println(query);
			stmt = (PreparedStatement) conn.prepareStatement(query);
			for (int i = 1; i <= maplist.size(); i++) {
				// step 3: assign value
				if (value.get(i - 1) instanceof String) {
					((PreparedStatement) stmt).setString(i,
							(String) value.get(i - 1));
				} else {
					((PreparedStatement) stmt).setObject(i,
							(Object) value.get(i - 1));
				}
			}
			((PreparedStatement) stmt).execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			
		}
		return true;
	}

	public boolean delete(String table, String where) {
		try {
			cdb = ConnectDB.getInstance();
			conn = cdb.connect();
			stmt = conn.createStatement();
			query = "DELETE FROM " + table + " WHERE " + where;
			// System.out.println(query);
			int delete = stmt.executeUpdate(query);
			if (delete == 1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return false;
	}
}
